package com.dao;

import com.bean.Project;
import com.bean.Table;
import com.db.DataBase;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProjectDao extends DataBase {

	private String tableName = "T_PROJECT";
	private boolean flag = false;
	
	public boolean add(Project obj){
		
		flag = true;
		StringBuilder sql  = new StringBuilder("INSERT ");
		sql.append( tableName);
		sql.append("(CREATE_TIME,NAME) ");
		sql.append("VALUES(NOW(),?)");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setString(index++, obj.getName().toUpperCase().trim());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag;
	}
	
	public boolean del(long id){
		
		flag = true;
		
		StringBuilder sql  = new StringBuilder("UPDATE ");
		sql.append( tableName );
		sql.append(" SET DEL_STATUS = 1");
		sql.append(" WHERE ID = ");
		sql.append(id);
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag;
	}
	
	public boolean update(Project obj){
		
		flag = true;
		StringBuilder sql = new StringBuilder("UPDATE ");
		sql.append(tableName);
		sql.append(" SET ");
		sql.append(" NAME = ? WHERE ID = ?");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setString(index++, obj.getName().toUpperCase().trim());
			preparedStatement.setLong(index++, obj.getId());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag ;
	}
	
	public Project get(Long id){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("ID,");
		sql.append("CREATE_TIME,");
		sql.append("NAME ");
		sql.append("FROM ");
		sql.append(tableName);
		sql.append(" WHERE DEL_STATUS = 0 AND ID = ? ");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setLong(index++, id);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				Project obj = new Project();
				obj.setId(resultSet.getLong("ID"));
				obj.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				obj.setName(resultSet.getString("NAME"));
				return obj;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
	}
	
	/**
	 * 
	 * @param name
	 * @return
	 */
	public Project getByName(String name){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("ID,");
		sql.append("CREATE_TIME,");
		sql.append("NAME ");
		sql.append("FROM ");
		sql.append(tableName);
		sql.append(" WHERE DEL_STATUS = 0 AND NAME = ? ");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setString(index++, name.trim().toUpperCase());
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				Project obj = new Project();
				obj.setId(resultSet.getLong("ID"));
				obj.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				obj.setName(resultSet.getString("NAME"));
				return obj;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
	}
	
	/**
	 * 
	 * @param name
	 * @return
	 */
	public List<Project> query(String name){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("ID,");
		sql.append("CREATE_TIME,");
		sql.append("NAME ");
		sql.append("FROM ");
		sql.append(tableName);
		sql.append(" WHERE 1=1 ");
		if(null != name && !"".equals(name)){
			sql.append("AND  NAME LIKE ? ");
		}
		sql.append("AND DEL_STATUS = 0 ");
		sql.append("ORDER BY ID ASC ");
		
		List<Project> objs = new ArrayList<Project>();
		
		try {
			
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			if(null != name && !"".equals(name)){
				preparedStatement.setString(index++, "%" + name.trim().toUpperCase() + "%");
			}
			resultSet = preparedStatement.executeQuery();
			
			Project obj;
			while(resultSet.next()){
				obj = new Project();
				obj.setId(resultSet.getLong("ID"));
				obj.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				obj.setName(resultSet.getString("NAME"));
				objs.add(obj);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		
		return objs;
	}

}
